package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.sl.au.entity.PartsAttributes;



public interface PartsAttributesRepository extends JpaRepository<PartsAttributes, String> {
	/**
	  * 从PartsAttributes表获取分组的下拉框选项
	  * @param group
	  * @return
	  */
//	 @Query("select o from PartsAttributes o where o.group = ?1 and o.title !='大型活动' and o.title !='大型活动安全防范' order by o.createTime asc")
//	 List<PartsAttributes> findByGroup(String group);
	 /**
	  * 从PartsAttributes表获取分组的下拉框选项
	  * @param group
	  * @return 返回String 集合
	  */
//	 @Query("select o.title from PartsAttributes o where o.group = ?1 order by o.createTime asc")
//	 List<String> findPartsAttributesByGroup(String group)
	 
	 /**
	  * 从PartsAttributes表通过获取分组和标题获取下拉框选项
	  * @param group
	  * @return
	  */
//	 @Query("select o from PartsAttributes o where o.group = ?1 and o.title = ?2 order by o.createTime asc")
//	 List<PartsAttributes> findByGroupAndTitle(String group, String title);
	 

	 /**
	 * @param key
	 * @param pageable
	 * @return 得到选项配置PartsAttributes的page，关键字为key
	 */
	@Query("SELECT a FROM PartsAttributes a "
			+ "where a.partsType.id like %?1% and"
			+ "(a.attributes_name like %?2% or a.attributes like %?2% or a.column_name like %?2%) "
			+ "order by a.partsType, MID(a.column_name,7,10) + 1")
	 Page<PartsAttributes> findPartsAttributes(String id,String key, Pageable pageable);
	 /**
	 * @param key
	 * @return 得到选项配置PartsAttributes的条数，关键字为key
	 */
	@Query(" select count(*) as num from PartsAttributes a "
			+ "where a.partsType.id like %?1% and"
			+ "(a.attributes_name like %?2% or a.attributes like %?2% or a.column_name like %?2%) ")
	 Long getPartsAttributesCount(String id,String key);
	
	/**
	 *通过typeid得到所有的参数
	 */
	@Query(" select o from PartsAttributes o where o.partsType.id = ?1 order by o.attributes")
	List<PartsAttributes> findAllPartsAtrributesByTypeid(String typeId);

	/**
	 *通过typeid得到所有的参数
	 */
	@Query(" select o from PartsAttributes o where o.partsType.id = ?1 order by  MID(o.column_name,7,10) + 1 ")
	List<PartsAttributes> findAllPartsAtrributesByTypeidOrderColumn(String typeId);
	
	
}
